package com.xzcedu.jdbc.transaction_;

import com.xzcedu.jdbc.utils.JDBCUtils;
import org.junit.jupiter.api.Test;

import java.sql.*;

/**
 * @author 徐州城
 * @version 1.0
 * 演示 JDBC中如何使用事务
 */
public class Transaction_ {

    @Test
    @SuppressWarnings("all")
    public void noTransaction() {

        //操作事务的
        //1. 得到连接
        Connection connection = null;

        //2. 组织sql语句
        String sql = "update account set balance = balance - 100 where id = ?";
        String sql2 = "update account set balance = balance + 100 where id = ?";


        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        //执行第二个sql
        PreparedStatement preparedStatement2 = null;
        try {
            connection = JDBCUtils.getConnection(); //在默认情况下，connection是默认自动提交 事务
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 1);
            //执行
            preparedStatement.executeUpdate();

            int i = 1 / 0;//抛出异常

            preparedStatement2 = connection.prepareStatement(sql2);
            preparedStatement2.setInt(1, 2);
            preparedStatement2.executeUpdate();//执行sql语句
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭连接
            JDBCUtils.close(resultSet, preparedStatement, connection);

            try {
                preparedStatement2.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Test
    @SuppressWarnings("all")
    public void useTransaction() {
//1. 得到连接
        Connection connection = null;

        //2. 组织sql语句
        String sql = "update account set balance = balance - 100 where id = ?";
        String sql2 = "update account set balance = balance + 100 where id = ?";


        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        //执行第二个sql
        PreparedStatement preparedStatement2 = null;
        try {
            connection = JDBCUtils.getConnection(); //在默认情况下，connection是默认自动提交 事务
            //将 connection 设置为不自动提交
            connection.setAutoCommit(false); //开启了事务
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 1);
            //执行
            preparedStatement.executeUpdate();

//            int i = 1 / 0;//抛出异常

            preparedStatement2 = connection.prepareStatement(sql2);
            preparedStatement2.setInt(1, 2);
            preparedStatement2.executeUpdate();//执行sql语句

            //最后提交事务
            connection.commit();
        } catch (Exception e) {
            //这里可以进行回滚，即撤销执行的 DML语句
            //默认回滚到事务的开始状态
            System.out.println("执行发生了异常，撤销执行的sql");
            try {
                connection.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            //关闭连接
            JDBCUtils.close(resultSet, preparedStatement, connection);

            try {
                preparedStatement2.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }

}
